﻿Imports System.Data.SqlClient

Module PruebaDataTables
    Dim dtSocios As New DataTable("Socio")
    Dim conexion As New SqlConnection("Server=USUARIO-PC\SQLSERVER;Database=Video;Trusted_Connection=True;")
    Dim adapter As SqlDataAdapter
    Sub Main()
        CreateDataTable()
        Console.WriteLine("Listado de socios: ")
        'FillADataTableWithADataReader()
        FillADataTableWithADataAdapter()
        ListSocios()
        MakeChangesToTable()
        Console.ReadKey()
    End Sub

    Private Sub CreateDataTable()
        dtSocios.Columns.Add("dni", Type.GetType("System.String"))
        dtSocios.Columns.Add("nombre", Type.GetType("System.String"))
        dtSocios.Columns.Add("apellido", Type.GetType("System.String"))
        dtSocios.Columns.Add("domicilio", Type.GetType("System.String"))
        dtSocios.Columns.Add("email", Type.GetType("System.String"))
        dtSocios.Columns.Add("telefono", Type.GetType("System.String"))
    End Sub

    Private Sub ListSocios()
        For Each row As DataRow In dtSocios.Rows
            Dim dni, nombre, apellido As String
            dni = row("dni").ToString()
            nombre = row("nombre").ToString()
            apellido = row("apellido").ToString()
            Console.WriteLine("{0}: {1}, {2}", dni, apellido, nombre)
        Next
    End Sub

    Private Sub FillADataTableWithADataReader()
        Dim selectCommand As SqlCommand = GetSelectCommand()
        conexion.Open()
        Dim lector As SqlDataReader = selectCommand.ExecuteReader()
        dtSocios.Load(lector)
        conexion.Close()
    End Sub

    Private Sub FillADataTableWithADataAdapter()
        adapter = New SqlDataAdapter()
        adapter.SelectCommand = GetSelectCommand()
        adapter.UpdateCommand = GetUpdateCommand()
        adapter.Fill(dtSocios)
    End Sub

    Private Function GetSelectCommand() As SqlCommand
        Dim selectCommand As New SqlCommand()
        selectCommand.CommandText = "SELECT dni, nombre, apellido, domicilio, email, telefono from Socio"
        selectCommand.Connection = conexion
        Return selectCommand
    End Function

    Private Function GetUpdateCommand() As SqlCommand
        Dim updateCommand As New SqlCommand()
        updateCommand.CommandText = "UPDATE Socio SET nombre = @nombre where Socio.dni = @dni"
        updateCommand.Parameters.Add("@nombre", SqlDbType.VarChar, 50)
        updateCommand.Parameters.Add("@dni", SqlDbType.VarChar, 50)
        updateCommand.Connection = conexion
        Return updateCommand
    End Function

    Private Sub MakeChangesToTable()
        Dim continuar As Boolean
        Do
            Console.Write("Ingrese el dni del socio cuyo nombre quiere modificar: ")
            Dim dni As String = Console.ReadLine()
            Console.Write("Ingrese el nuevo nombre")
            Dim nuevoNombre = Console.ReadLine()
            Dim encontrado As Boolean
            For Each row As DataRow In dtSocios.Rows
                If row("dni").ToString() = dni Then
                    row.BeginEdit()
                    row("dni") = nuevoNombre
                    row.EndEdit()
                    encontrado = True
                End If
            Next
            If encontrado Then
                adapter.UpdateCommand.Parameters("@dni").Value = dni
                adapter.UpdateCommand.Parameters("@nombre").Value = nuevoNombre
                adapter.Update(dtSocios)
                Console.WriteLine("Socio actualizado")
            Else
                Console.WriteLine("El socio no se encontró")
            End If
            Console.WriteLine("Continuar: y/n")
            continuar = Console.ReadLine().Contains("y")
        Loop While continuar
    End Sub
End Module
